#! /bin/bash
export LANG=zh_CN.UTF-8
HIVE_HOME=/usr/bin/hive


${HIVE_HOME} -S -e "
create database IF NOT EXISTS ol_edu_rpt;

--每天/每月/每年线上线下以及新老学员的意向用户个数
drop table if exists ol_edu_rpt.rpt_intent_count;
create table if not exists ol_edu_rpt.rpt_intent_count
(
        date_time string,
        day string,
        month string,
        year string,
        time_type string comment'统计时间维度',
        clue_state string comment'新老学员',
        origin_type string comment'线上线下',
        intent_avl_count bigint comment '意向用户个数'
)comment '线上线下以及新老学员的意向用户个数'
row format delimited
fields terminated by '\t'
stored as orc tblproperties ('orc.compress'='snappy');

--每天/每月/每年各地区的线上线下以及新老学员的意向用户个数
drop table if exists ol_edu_rpt.rpt_intent_area_count;
create table if not exists ol_edu_rpt.rpt_intent_area_count
(
        date_time string,
        day string,
        month string,
        year string,
        time_type string comment'统计时间维度',
        area string comment'地区',
        clue_state string comment'新老学员',
        origin_type string comment'线上线下',
        intent_avl_count bigint comment '意向用户个数'
)comment '各地区线上线下以及新老学员的意向用户个数'
row format delimited
fields terminated by '\t'
stored as orc tblproperties ('orc.compress'='snappy');

--每天/每月/每年各学科线上线下以及新老学员的意向用户个数
drop table if exists ol_edu_rpt.rpt_intent_subject_count;
create table if not exists ol_edu_rpt.rpt_intent_subject_count
(
        date_time string,
        day string,
        month string,
        year string,
        time_type string comment'统计时间维度',
        itcast_subject_id int comment'学科id',
        itcast_subject string comment'学科',
        clue_state string comment'新老学员',
        origin_type string comment'线上线下',
        intent_avl_count bigint comment '意向用户个数'
)comment '各学科线上线下以及新老学员的意向用户个数'
row format delimited
fields terminated by '\t'
stored as orc tblproperties ('orc.compress'='snappy');

--每天/每月/每年各校区线上线下以及新老学员的意向用户个数
drop table if exists ol_edu_rpt.rpt_intent_school_count;
create table if not exists ol_edu_rpt.rpt_intent_school_count
(
        date_time string,
        day string,
        month string,
        year string,
        time_type string comment'统计时间维度',
        itcast_school_id int comment'校区id',
        itcast_school string comment'校区',
        clue_state string comment'新老学员',
        origin_type string comment'线上线下',
        intent_avl_count bigint comment '意向用户个数'
)comment '各校区线上线下以及新老学员的意向用户个数'
row format delimited
fields terminated by '\t'
stored as orc tblproperties ('orc.compress'='snappy');

--每天/每月/每年各来源渠道线上线下以及新老学员的意向用户个数
drop table if exists ol_edu_rpt.rpt_intent_origin_count;
create table if not exists ol_edu_rpt.rpt_intent_origin_count
(
        date_time string,
        day string,
        month string,
        year string,
        time_type string comment'统计时间维度',
        origin_channel string comment'来源渠道',
        clue_state string comment'新老学员',
        origin_type string comment'线上线下',
        intent_avl_count bigint comment '意向用户个数'
)comment '各来源渠道线上线下以及新老学员的意向用户个数'
row format delimited
fields terminated by '\t'
stored as orc tblproperties ('orc.compress'='snappy');

--每天/每月/每年各咨询中心线上线下以及新老学员的意向用户个数
drop table if exists ol_edu_rpt.rpt_intent_depart_count;
create table if not exists ol_edu_rpt.rpt_intent_depart_count
(
        date_time string,
        day string,
        month string,
        year string,
        time_type string comment'统计时间维度',
        department_id int comment'咨询中心id',
        department_name string comment'咨询中心',
        clue_state string comment'新老学员',
        origin_type string comment'线上线下',
        intent_avl_count bigint comment '意向用户个数'
)comment '各咨询中心线上线下以及新老学员的意向用户个数'
row format delimited
fields terminated by '\t'
stored as orc tblproperties ('orc.compress'='snappy');

--每天线上线下及新老学员的有效线索个数
drop table if exists ol_edu_rpt.rpt_clue_day_count;
create table if not exists ol_edu_rpt.rpt_clue_day_count
(
        date_time string,
        day string,
        time_type string comment'统计时间维度',
        clue_state string comment'新老学员',
        origin_type string comment'线上线下',
        clue_avl_count bigint comment '有效线索个数'
)comment '每天线上线下及新老学员的有效线索个数'
row format delimited
fields terminated by '\t'
stored as orc tblproperties ('orc.compress'='snappy');

--每小时线上线下及新老学员的有效线索转化率 = 有效线索个数 / 总线索个数
drop table if exists ol_edu_rpt.rpt_clue_hour_rate;
create table if not exists ol_edu_rpt.rpt_clue_hour_rate
(
        date_time string,
        hour string,
        group_type string comment'分组字段',
        clue_state string comment'新老学员',
        origin_type string comment'线上线下',
        eff_con_rate decimal(5,2) comment '有效线索转化率'
)comment '每天线上线下及新老学员的有效线索个数'
row format delimited
fields terminated by '\t'
stored as orc tblproperties ('orc.compress'='snappy');
"